﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using CWB.Common.Constant;
using CWB.Common.DTO;
using CWB.Common.Logging;
using CWB.Common.Utilities;
using CWB.Web.Models;
using System.Runtime.Serialization;

namespace CWB.Web.DB
{
    [DataContract]
    public class DbModuleProd
    {
        [DataMember(Name = "sn")]
        public int sn;
        [DataMember(Name = "pn")]
        public string prodName;
        [DataMember(Name = "id")]
        public string prodid;
        [DataMember(Name = "u")]
        public int unit;
        [DataMember(Name = "f")]
        public int fee;
        [DataMember(Name = "v")]
        public int unitvalue;
    }

    public class DbTableProduct : BaseDAO
    {
        private readonly static ISysLog log = SysLog.GetLogger(typeof(DbTableProduct));

        public DbTableProduct()
        {
            base.init(typeof(DbTableProduct).ToString(), DataBaseName.Org);
        }

        public bool CheckIdIsExist(string id)
        {
            int nums = ExecuteReaderCount<string>("proditems", "prodid", id);
            if (nums == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }



        private List<DbModuleProd> getModuleProdCallBack(SQLiteDataReader sdr)
        {
            List<DbModuleProd> listbbu = new List<DbModuleProd>() { };
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    listbbu.Add(new DbModuleProd()
                    {
                        prodName = sdr["prodName"].ToString()
                        ,
                        prodid = sdr["prodID"].ToString()
                        ,
                        sn = Convert.ToInt32(sdr["sn"].ToString())
                        ,
                        unit = Convert.ToInt32(sdr["unit"].ToString())
                        ,
                        fee = Convert.ToInt32(sdr["fee"].ToString())
                        ,
                        unitvalue = Convert.ToInt32(sdr["unitvalue"].ToString())
                    });
                }
            }
            return listbbu;
        }

        public DbModuleProd getProdItemBySn(int sn)
        {
            const string strCMD = "select * from proditems where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", sn));
            List<DbModuleProd> itemList = ExecuteReader<DbModuleProd>(CommandType.Text, strCMD, listPara, getModuleProdCallBack);
            if (itemList.Count > 0)
            {
                return itemList[0];
            }
            else
            {
                Exception e = new Exception(string.Format("Data not found. proditems.sn : [{0}]", sn));
                log.Exception(e);
                throw (e);
            }
        }

        public DbModuleProd getProdItemById(string id)
        {
            const string strCMD = "select * from proditems where id= @id";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@id", id));
            List<DbModuleProd> itemList = ExecuteReader<DbModuleProd>(CommandType.Text, strCMD, listPara, getModuleProdCallBack);
            if (itemList.Count > 0)
            {
                return itemList[0];
            }
            else
            {
                Exception e = new Exception(string.Format("Data not found. proditems.id : [{0}]", id));
                log.Exception(e);
                throw (e);
            }
        }

        public bool createProdItem(DbModuleProd obj)
        {
            const string strCMD = "insert into proditems (unit, prodid, prodname, fee, unitvalue) values (@unit, @prodid, @prodname, @fee, @unitvalue)";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@unit", obj.unit));
            listPara.Add(new SQLiteParameter("@prodid", obj.prodid));
            listPara.Add(new SQLiteParameter("@prodname", obj.prodName));
            listPara.Add(new SQLiteParameter("@fee", obj.fee));
            listPara.Add(new SQLiteParameter("@unitvalue", obj.unitvalue));
            int icnt = ExecuteNonQuery(strCMD, listPara);
            return (icnt > 0);
        }

        public bool updateProdItemBySN(DbModuleProd obj)
        {
            const string strCMD = "update proditems set unit = @unit, prodname = @prodname, fee = @fee, unitvalue = @unitvalue where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", obj.sn));
            listPara.Add(new SQLiteParameter("@unit", obj.unit));
            listPara.Add(new SQLiteParameter("@prodname", obj.prodName));
            listPara.Add(new SQLiteParameter("@fee", obj.fee));
            listPara.Add(new SQLiteParameter("@unitvalue", obj.unitvalue));
            int icnt = ExecuteNonQuery(strCMD, listPara);
            return (icnt > 0);
        }

        public bool DeleteProdItemBySn(int sn)
        {
            const string strCMD = "delete from proditems where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", sn));
            int icnt = ExecuteNonQuery(strCMD, listPara);
            return (icnt > 0);
        }

        public List<DbModuleProd> getAllProdItem(ref Paging pg, KeyValuePair<string, string> SearchPara, DBSortingPara dbSortingPara)
        {
            string sqlMain = "Select * from proditems";
            string sqlWehre = default(string);
            string sqlOrderby = default(string);
            List<SQLiteParameter> sqlPara = new List<SQLiteParameter>() { };
            if (!string.IsNullOrEmpty(SearchPara.Key) && !string.IsNullOrEmpty(SearchPara.Value))
            {
                string key = SearchPara.Key;
                sqlWehre = string.Format(" Where {0} = @{1}", key, key);
                sqlPara.Add(new SQLiteParameter(string.Format("@{0}", key), SearchPara.Value));
            }
            sqlOrderby = dbSortingPara.getOrderByHLQ();

            //get totalRecord
            string sqlCount = string.Format("select count(*) as rcnt from {0}", sqlMain.Split(' ').Last());
            string strCMD = string.Format("{0} {1}", sqlCount, sqlWehre);
            int count = ExecuteReaderCount(strCMD, sqlPara);
            pg.totalRecord = count;

            //get data
            strCMD = string.Format("{0} {1} {2} {3}", sqlMain, sqlWehre, sqlOrderby, pg.getLimitSql());
            List<DbModuleProd> listProdItem = ExecuteReader<DbModuleProd>(CommandType.Text, strCMD, sqlPara, getModuleProdCallBack);

            return listProdItem;
        }

        public List<DbModuleProd> getSupportedProdItems(int uplineSN)
        {
            if (uplineSN == 0)
            {
                return getAllPordItems();
            }
            else
            {
                string strsql = string.Format(@"
                    Select proditems.* from proditems 
                    inner join AgentProdItems on proditems.sn = AgentProdItems.ProdItemSN
                    where AgentProdItems.AgentSN = {0}
                    order by proditems.prodid ASC
                ", uplineSN);

                //get data
                List<DbModuleProd> listProdItem = ExecuteReader<DbModuleProd>(CommandType.Text, strsql, null, getModuleProdCallBack);
                return listProdItem;
            }
        }

        public List<DbModuleProd> getAllPordItems()
        {
            string sqlMain = "Select * from proditems";
            string sqlOrderby = default(string);
            DBSortingPara SortingPara = new DBSortingPara();
            SortingPara.AddSortingItem("prodid", SortingType.ASC);
            sqlOrderby = SortingPara.getOrderByHLQ();

            //get data
            string strCMD = string.Format("{0} {1} ", sqlMain, sqlOrderby);
            List<DbModuleProd> listProdItem = ExecuteReader<DbModuleProd>(CommandType.Text, strCMD, null, getModuleProdCallBack);

            return listProdItem;
        }
    
    }
}
